﻿CREATE FUNCTION [dbo].[GetContentTargetInformation]
	(
		@TargetID AS uniqueidentifier,
		@ScopeID AS int
	)  
RETURNS 
	@Infomation TABLE 
	(
		
		WebApplication varchar(100),
		SiteCollectionUrl varchar(100),
		SiteUrl varchar(500),
		ListTitle varchar(500),
		ListUrl varchar(500),
		ServerName varchar(200),
		DataBaseName varchar(200)
		)
AS
BEGIN
	

	IF (@ScopeID=1)
	BEGIN
			INSERT INTO @Infomation
			SELECT
			'' AS ServerName,
			'' AS DataBaseName,
			'' AS WebApplication,
			'' AS SiteCollection,
			'' AS Site,
			'' AS ListTitle,
			'' AS ListUrl
				

	END
	ELSE IF (@ScopeID=2)
	BEGIN
			INSERT INTO @Infomation
			SELECT
				w.DisplayName AS WebApplication,
			'' AS SiteCollection,
			'' AS Site,
			'' AS ListTitle,
			'' AS ListUrl,
			'' AS ServerName,
			'' AS DataBaseName
			FROM	
				WebApplication w
				
			WHERE
				WebApplicationID = @TargetID
	
	END
	ELSE IF (@ScopeID=3)
	BEGIN
			INSERT INTO @Infomation
			SELECT
				w.DisplayName AS WebApplication,
			s.UpgradeRedirectUri AS SiteCollection,
			'' AS Site,
			'' AS ListTitle,
			'' AS ListUrl,
			'' AS ServerName,
			'' AS DataBaseName
			FROM	
				WebApplication w
					INNER JOIN
				SiteCollection s ON w.WebApplicationID = s.WebApplicationID
				
			WHERE
				s.SiteCollectionID = @TargetID
	
	END
	ELSE IF (@ScopeID=4)
	BEGIN
			INSERT INTO @Infomation
			SELECT
				wa.DisplayName AS WebApplication,
			sc.UpgradeRedirectUri AS SiteCollection,
			
			w.ServerRelativeUrl AS Site,
			'' AS ListTitle,
			'' AS ListUrl,
			'' AS ServerName,
			'' AS DataBaseName
			FROM	
				WebApplication wa
					INNER JOIN
				SiteCollection sc ON wa.WebApplicationID = sc.WebApplicationID
					INNER JOIN
				Web w ON sc.SiteCollectionID = w.SiteCollectionID
				
			WHERE
				w.WebID = @TargetID
	
	END
	ELSE IF (@ScopeID=5)
	BEGIN
			INSERT INTO @Infomation
			SELECT
				wa.DisplayName AS WebApplication,
			sc.UpgradeRedirectUri AS SiteCollection,
			
			w.ServerRelativeUrl AS Site,
			l.Title AS ListTitle,
			l.URL AS ListUrl,
			'' AS ServerName,
			'' AS DataBaseName
			FROM	
				WebApplication wa
					INNER JOIN
				SiteCollection sc ON wa.WebApplicationID = sc.WebApplicationID
					INNER JOIN
				Web w ON sc.SiteCollectionID = w.SiteCollectionID
					INNER JOIN
				List l ON l.WebID = w.WebID
				
			WHERE
				l.ListID = @TargetID
	
	END
	ELSE IF (@ScopeID=6)
	BEGIN
			INSERT INTO @Infomation
			SELECT
				'' AS WebApplication,
			'' AS SiteCollection,
			
			'' AS Site,
			'' AS ListTitle,
			'' AS ListUrl,
			'' AS ServerName,
			d.DisplayName AS DataBaseName
			FROM	
				[Database] d
				
			WHERE
				d.DatabaseID = @TargetID
	END
	ELSE IF (@ScopeID=7)
	BEGIN
			INSERT INTO @Infomation
			SELECT
				'' AS WebApplication,
			'' AS SiteCollection,
			
			'' AS Site,
			'' AS ListTitle,
			'' AS ListUrl,
			s.Name AS ServerName,
			'' AS DataBaseName
			FROM	
				Server s
				
			WHERE
				s.ServerID = @TargetID
	
	END


	RETURN
END